using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;
using System.Text;

namespace PCSComProduction.WorkOrder.DS
{
	/// <summary>
	/// Summary description for PRO_RequiredMaterialDetailDS.
	/// </summary>
	public class PRO_RequiredMaterialDetailDS
	{
		private const string THIS = "PCSComProduction.WorkOrder.DS.PRO_RequiredMaterialDetailDS";
		public PRO_RequiredMaterialDetailDS()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public Object GetObjectVO(int pintID)
		{
			throw new NotImplementedException();
		}

		public DataSet List()
		{
			throw new NotImplementedException();
		}
		public DataSet List(int pintMastterID)
		{
			throw new NotImplementedException();
		}
		/// <summary>
		/// Delete by Master ID
		/// </summary>
		/// <param name="pintID">Required Master ID</param>
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				string strSql=	"DELETE " + PRO_RequiredMaterialDetailTable.TABLE_NAME 
					+ " WHERE  " + PRO_RequiredMaterialDetailTable.REQUIREDMATERIALMASTERID_FLD + "=" + pintID;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(Object pobjObjecVO)
		{
			throw new NotImplementedException();
		}

		public void Add(Object pobjObjectVO)
		{
			throw new NotImplementedException();
		}

		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS =null;
			OleDbCommandBuilder odcbPCS ;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql=	"SELECT "
					+ PRO_RequiredMaterialDetailTable.PRODUCTID_FLD + ","
					+ PRO_RequiredMaterialDetailTable.QUANTITY_FLD + ","
					+ PRO_RequiredMaterialDetailTable.REQUIREDMATERIALDETAIID_FLD + ","
					+ PRO_RequiredMaterialDetailTable.REQUIREDMATERIALMASTERID_FLD + ","
					+ PRO_RequiredMaterialDetailTable.REQUIREDQUANTITY_FLD + ","
					+ PRO_RequiredMaterialDetailTable.STOCKQUANTITY_FLD + ","
					+ PRO_RequiredMaterialDetailTable.WORKORDERDETAILID_FLD 
					+ "  FROM " + PRO_RequiredMaterialDetailTable.TABLE_NAME;

				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData,PRO_RequiredMaterialDetailTable.TABLE_NAME);

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
					{
																   
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);				
					}
					else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{

						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
					else
					{
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
					}
				}
				else
				{
					throw ex;
				}
			}			
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void UpdateTotalRequired(int pintSaleOrderdDetailID, string pstrWorkOrderDetailList, string pstrProductList)
		{
			const string METHOD_NAME = THIS + ".UpdateTotalRequired()";
			OleDbConnection oconPCS =null;

			try
			{
                StringBuilder strSql = new StringBuilder();
                strSql.Append("UPDATE PRO_RequiredMaterialDetail");
                strSql.Append("  SET RequiredQty =");
                strSql.Append("  (SELECT SUM(Qty) FROM PRO_RequiredMaterialDetail D JOIN PRO_RequiredMaterialMaster M");
                strSql.Append("  ON D.RequiredMaterialMasterID = M.RequiredMaterialMasterID");
                strSql.Append("  WHERE D.ProductID = PRO_RequiredMaterialDetail.ProductID");
                strSql.Append("  AND M.SaleOrderDetailID = " + pintSaleOrderdDetailID);
                strSql.Append("  AND D.WorkOrderDetailID = PRO_RequiredMaterialDetail.WorkOrderDetailID");
                strSql.Append("  AND IssuePurposeID = " + (int)PurposeEnum.Plan + ")");
                strSql.Append("  WHERE RequiredMaterialMasterID IN (SELECT RequiredMaterialMasterID FROM PRO_RequiredMaterialMaster");
                strSql.Append("  WHERE SaleOrderDetailID = " + pintSaleOrderdDetailID);
                strSql.Append("  AND ProductID IN (" + pstrProductList + ")");
                strSql.Append("  AND WorkOrderDetailID IN (" + pstrWorkOrderDetailList + ")");
                strSql.Append("  AND IssuePurposeID = " + (int)PurposeEnum.Plan + ")");

				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				OleDbCommand ocmdPCS = new OleDbCommand(strSql.ToString(), oconPCS);
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
					else if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
				{
					throw ex;
				}
			}			
			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataSet GetDetailByMaster(int requiredMaterialMasterId)
		{
			const string METHOD_NAME = THIS + ".GetDetailByMaster()";
			DataSet dstDetail = new DataSet();
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql= "Select Distinct 0 as Line, WOM.WorkOrderNo, PM.Code as WO_Item, "
					+ " PM.Description as WO_PartName, WOD.OrderQuantity AS WO_Qty, WOD.ProductionLineID, R.RequiredMaterialDetailID,"
					+ " R.RequiredMaterialMasterID,R.WorkOrderDetailID,  P.Code as Bom_Item,P.Description as BOm_PartName,"
					+ " P.DesignUMID,UM.Code Unit, WOD.OrderQuantity*WOB.Quantity SLdutru,R.Qty,"
					+ " R.StockQty, R.RequiredQty, R.ProductID, WOD.ProductID AS WO_ProductID, R.ActualReceived,P.Rate "
					+ " FROM "+ PRO_RequiredMaterialDetailTable.TABLE_NAME + " R"
					+ " inner join ITM_Product P ON R.ProductID = P.ProductID"
					+ " inner join PRO_WorkOrderDetail WOD ON R.WorkOrderDetailID = WOD.WorkOrderDetailID"
					+ " INNER JOIN PRO_WOBOM WOB ON WOD.WorkOrderDetailID = WOB.WorkOrderDetailID"
					+ " AND WOB.ComponentID = R.ProductID"
					+ " Inner join ITM_Product PM ON WOD.ProductID = PM.ProductID"
					+ " inner join PRO_WorkOrderMaster WOM ON WOD.WorkOrderMasterID = WOM.WorkOrderMasterID"
					+ " LEFT join MST_UnitOfMeasure UM ON P.DesignUMID = UM.UnitOfMeasureID"
					+ " WHERE " + PRO_RequiredMaterialDetailTable.REQUIREDMATERIALMASTERID_FLD + " = " + requiredMaterialMasterId;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstDetail,PRO_RequiredMaterialDetailTable.TABLE_NAME);

				return dstDetail;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetSlipData(int pintMasterID)
		{
			const string METHOD_NAME = THIS + ".GetSlipData()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql= "SELECT TransDate, TransNo, PU.Description Purpose, PRO.Code ProductionLine,"
					+ " S.FGoodsCode, P.Description FPartNumber,"
					+ " C.Description ComName, C.Code ComCode, U.Code Unit, SUM(Qty) Qty, SUM(Qty) * ISNULL(C.Rate,1) StockQty,"
					+ " M.UserApproval, M.Username"
					+ " FROM PRO_RequiredMaterialDetail D JOIN PRO_RequiredMaterialMaster M"
					+ " ON D.RequiredMaterialMasterID = M.RequiredMaterialMasterID"
					+ " JOIN PRO_IssuePurpose PU ON PU.IssuePurposeID = M.IssuePurposeID"
					+ " JOIN SO_SaleOrderDetail S ON M.SaleOrderDetailID = S.SaleOrderDetailID"
					+ " JOIN PRO_ProductionLine PRO ON M.ProductionLineID = PRO.ProductionLineID"
					+ " JOIN ITM_Product P ON S.ProductID = P.ProductID"
					+ " JOIN ITM_Product C ON D.ProductID = C.ProductID"
					+ " LEFT JOIN MST_UnitOfMeasure U ON C.DesignUMID = U.UnitOfMeasureID"
					+ " WHERE M.RequiredMaterialMasterID = " + pintMasterID
					+ " GROUP BY TransDate, TransNo, PU.Description, PRO.Code, S.FGoodsCode, P.Description,"
					+ " C.Description, C.Code, C.Rate, U.Code, M.UserApproval, M.Username";
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				DataTable dtbData = new DataTable(PRO_RequiredMaterialDetailTable.TABLE_NAME);
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		public DataTable GetRequiredBySale(int pintSaleOrderDetailID)
		{
			const string METHOD_NAME = THIS + ".GetRequiredBySale()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql= "SELECT WOD.WorkOrderDetailID, WOB.ComponentID ProductID,"
					+ " SUM(WOD.OrderQuantity * WOB.Quantity) AS SLdutru, RequiredQty"
					+ " FROM PRO_WorkOrderDetail WOD JOIN PRO_WOBOM WOB ON WOD.WorkOrderDetailID = WOB.WorkOrderDetailID"
					+ " LEFT JOIN (SELECT RD.WorkOrderDetailID, RD.ProductID, SUM(ISNULL(Qty,0)) RequiredQty"
					+ " FROM PRO_RequiredMaterialDetail RD JOIN PRO_RequiredMaterialMaster RM"
					+ " ON RD.RequiredMaterialMasterID = RM.RequiredMaterialMasterID"
					+ " WHERE RM.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ " GROUP BY RD.WorkOrderDetailID, RD.ProductID) RD"
					+ " ON WOB.ComponentID = RD.ProductID"
					+ " AND WOD.WorkOrderDetailID = RD.WorkOrderDetailID"
					+ " WHERE WOD.SaleOrderDetailID = " + pintSaleOrderDetailID
					+ " GROUP BY WOD.WorkOrderDetailID, WOB.ComponentID, RequiredQty";
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				DataTable dtbData = new DataTable(PRO_RequiredMaterialDetailTable.TABLE_NAME);
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);

				return dtbData;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}
